package com.student.health.utils;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.alibaba.fastjson.TypeReference;
import com.student.health.entity.Foods;
import com.student.health.entity.FoodsDetail;
import com.student.health.utils.DTO.TemplateInfo;
import com.student.health.utils.DTO.TranTemplate;

import java.io.FileWriter;
import java.io.IOException;
import java.nio.file.Files;
import java.nio.file.Paths;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class HealthFoodGet {
    public static void main(String[] args) throws Exception {
        //insertFood();
        //insertFoodDetail();
        //getFoodsDetailData();
        //insertFoodDetail();
        testWeChat();
    }

    private static void testWeChat(){
        String appId = "wx1ec4cc6b9084d0dd";
        String appSecret = "55afc7353f660846cf536cc23b0f08cb";
        String templateId= "wnXYfqmxWvpEWwV6LAF42JRTv6OMlfE7DjPZH3UPQME";
        String openId = "ozY8O6yKo1ld5FjN-4NaZUonwDis";
        //获取token
        String tokenRes = HttpClientUtil.doGet("https://api.weixin.qq.com/cgi-bin/token?grant_type=client_credential&appid=" + appId + "&secret=" + appSecret);
        JSONObject jsonObject = JSONObject.parseObject(tokenRes);
        String access_token = jsonObject.getString("access_token");

        //发送模板消息POST https://api.weixin.qq.com/cgi-bin/message/template/send?access_token=ACCESS_TOKEN
        TranTemplate tranTemplate = new TranTemplate();
        tranTemplate.setTouser(openId);
        tranTemplate.setTemplate_id(templateId);
        Map<String , TemplateInfo> map = new HashMap<>();


        TemplateInfo templateInfo = new TemplateInfo();
        templateInfo.setValue("早餐");
        templateInfo.setColor("#173177");
        map.put("first",templateInfo);

        TemplateInfo templateInfo2 = new TemplateInfo();
        templateInfo2.setValue("苹果");
        templateInfo2.setColor("#DC143C");
        map.put("remark",templateInfo2);

        tranTemplate.setData(map);

        String data = JSON.toJSONString(tranTemplate);



        HttpClientUtil.doPostJson("https://api.weixin.qq.com/cgi-bin/message/template/send?access_token="+ access_token,data);
    }


    private static void insertFood() throws Exception{
        //List<Foods> foodsList = getFoodDataJson();
        List<Foods> foodsList = getFoodData();
        Class.forName("com.mysql.jdbc.Driver");
        Connection conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/healthmanager?rewriteBatchedStatements=true&useSSL=false", "root", "root");
        String sql = "INSERT INTO foods (food_id, name, health_level, calory,category) VALUES (?, ?, ?, ?,?)";
        PreparedStatement prep = conn.prepareStatement(sql);
        try {
            // 将连接的自动提交关闭，数据在传送到数据库的过程中相当耗时
            conn.setAutoCommit(false);
            //获取每个分类下的食物
            for (Foods foodDatum : foodsList) {
                prep.setString(1,foodDatum.getFoodId());
                prep.setString(2, foodDatum.getName());
                prep.setInt(3, foodDatum.getHealthLevel());
                prep.setBigDecimal(4, foodDatum.getCalory());
                prep.setInt(5, foodDatum.getCategory());
                prep.addBatch();
            }

            prep.executeBatch();
            prep.clearBatch();
            conn.commit();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            prep.close();
            conn.close();
        }
    }


    private static void insertFoodDetail() throws Exception{
        List<FoodsDetail> foodDetailsJson = getFoodDetailsJson();
        Class.forName("com.mysql.jdbc.Driver");
        Connection conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/healthmanager?rewriteBatchedStatements=true&useSSL=false", "root", "root");
        String sql = "INSERT INTO food_nutrition (food_id,name,calory,calory_unit,joule,joule_unit,protein,protein_unit,fat,fat_unit,saturated_fat,saturated_fat_unit,fatty_acid,fatty_acid_unit,mufa,mufa_unit,pufa,pufa_unit,cholesterol,cholesterol_unit,carbohydrate,carbohydrate_unit,sugar,sugar_unit,fiber_dietary,fiber_dietary_unit,natrium,natrium_unit,alcohol,alcohol_unit,vitamin_a,vitamin_a_unit,carotene,carotene_unit,vitamin_d,vitamin_d_unit,vitamin_e,vitamin_e_unit,vitamin_k,vitamin_k_unit,thiamine,thiamine_unit,lactoflavin,lactoflavin_unit,vitamin_b6,vitamin_b6_unit,vitamin_b12,vitamin_b12_unit,vitamin_c,vitamin_c_unit,niacin,niacin_unit,folacin,folacin_unit,pantothenic,pantothenic_unit,biotin,biotin_unit,choline,choline_unit,phosphor,phosphor_unit,kalium,kalium_unit,magnesium,magnesium_unit,calcium,calcium_unit,iron,iron_unit,zinc,zinc_unit,iodine,iodine_unit,selenium,selenium_unit,copper,copper_unit,fluorine,fluorine_unit,manganese,manganese_unit,health_light,health_tips,health_suggest,gi_value,gi_label,gl_value,gl_label) " +
        "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
        PreparedStatement prep = conn.prepareStatement(sql);
        try {
            // 将连接的自动提交关闭，数据在传送到数据库的过程中相当耗时
            conn.setAutoCommit(false);
            //获取每个分类下的食物
            for (FoodsDetail foodDatum : foodDetailsJson) {
                prep.setString(1,foodDatum.getFoodId());
                prep.setString(2, foodDatum.getName());
                prep.setBigDecimal(3, foodDatum.getCalory());
                prep.setString(4, foodDatum.getCaloryUnit());
                prep.setBigDecimal(5, foodDatum.getJoule());
                prep.setString(6, foodDatum.getJouleUnit());
                prep.setBigDecimal(7, foodDatum.getProtein());
                prep.setString(8, foodDatum.getProteinUnit());
                prep.setBigDecimal(9, foodDatum.getFat());
                prep.setString(10, foodDatum.getFatUnit());
                prep.setBigDecimal(11, foodDatum.getSaturatedFat());
                prep.setString(12, foodDatum.getSaturatedFatUnit());
                prep.setBigDecimal(13, foodDatum.getFattyAcid());
                prep.setString(14, foodDatum.getFattyAcidUnit());
                prep.setBigDecimal(15, foodDatum.getMufa());
                prep.setString(16, foodDatum.getMufaUnit());
                prep.setBigDecimal(17, foodDatum.getPufa());
                prep.setString(18, foodDatum.getPufaUnit());
                prep.setBigDecimal(19, foodDatum.getCholesterol());
                prep.setString(20, foodDatum.getCholesterolUnit());
                prep.setBigDecimal(21, foodDatum.getCarbohydrate());
                prep.setString(22, foodDatum.getCarbohydrateUnit());
                prep.setBigDecimal(23, foodDatum.getSugar());
                prep.setString(24, foodDatum.getSugarUnit());
                prep.setBigDecimal(25, foodDatum.getFiberDietary());
                prep.setString(26, foodDatum.getFiberDietaryUnit());
                prep.setBigDecimal(27, foodDatum.getNatrium());
                prep.setString(28, foodDatum.getNatriumUnit());
                prep.setBigDecimal(29, foodDatum.getAlcohol());
                prep.setString(30, foodDatum.getAlcoholUnit());
                prep.setBigDecimal(31, foodDatum.getVitaminA());
                prep.setString(32, foodDatum.getVitaminAUnit());
                prep.setBigDecimal(33, foodDatum.getCarotene());
                prep.setString(34, foodDatum.getCaroteneUnit());
                prep.setBigDecimal(35, foodDatum.getVitaminD());
                prep.setString(36, foodDatum.getVitaminDUnit());
                prep.setBigDecimal(37, foodDatum.getVitaminE());
                prep.setString(38, foodDatum.getVitaminEUnit());
                prep.setBigDecimal(39, foodDatum.getVitaminK());
                prep.setString(40, foodDatum.getVitaminKUnit());
                prep.setBigDecimal(41, foodDatum.getThiamine());
                prep.setString(42, foodDatum.getThiamineUnit());
                prep.setBigDecimal(43, foodDatum.getLactoflavin());
                prep.setString(44, foodDatum.getLactoflavinUnit());
                prep.setBigDecimal(45, foodDatum.getVitaminB6());
                prep.setString(46, foodDatum.getVitaminB6Unit());
                prep.setBigDecimal(47, foodDatum.getVitaminB12());
                prep.setString(48, foodDatum.getVitaminB12Unit());
                prep.setBigDecimal(49, foodDatum.getVitaminC());
                prep.setString(50, foodDatum.getVitaminCUnit());
                prep.setBigDecimal(51, foodDatum.getNiacin());
                prep.setString(52, foodDatum.getNiacinUnit());
                prep.setBigDecimal(53, foodDatum.getFolacin());
                prep.setString(54, foodDatum.getFolacinUnit());
                prep.setBigDecimal(55, foodDatum.getPantothenic());
                prep.setString(56, foodDatum.getPantothenicUnit());
                prep.setBigDecimal(57, foodDatum.getBiotin());
                prep.setString(58, foodDatum.getBiotinUnit());
                prep.setBigDecimal(59, foodDatum.getCholine());
                prep.setString(60, foodDatum.getCholineUnit());
                prep.setBigDecimal(61, foodDatum.getPhosphor());
                prep.setString(62, foodDatum.getPhosphorUnit());
                prep.setBigDecimal(63, foodDatum.getKalium());
                prep.setString(64, foodDatum.getKaliumUnit());
                prep.setBigDecimal(65, foodDatum.getMagnesium());
                prep.setString(66, foodDatum.getMagnesiumUnit());
                prep.setBigDecimal(67, foodDatum.getCalcium());
                prep.setString(68, foodDatum.getCalciumUnit());
                prep.setBigDecimal(69, foodDatum.getIron());
                prep.setString(70, foodDatum.getIronUnit());
                prep.setBigDecimal(71, foodDatum.getZinc());
                prep.setString(72, foodDatum.getZincUnit());
                prep.setBigDecimal(73, foodDatum.getIodine());
                prep.setString(74, foodDatum.getIodineUnit());
                prep.setBigDecimal(75, foodDatum.getSelenium());
                prep.setString(76, foodDatum.getSeleniumUnit());
                prep.setBigDecimal(77, foodDatum.getCopper());
                prep.setString(78, foodDatum.getCopperUnit());
                prep.setBigDecimal(79, foodDatum.getFluorine());
                prep.setString(80, foodDatum.getFluorineUnit());
                prep.setBigDecimal(81, foodDatum.getManganese());
                prep.setString(82, foodDatum.getManganeseUnit());
                prep.setInt(83, foodDatum.getHealthLight());
                prep.setString(84, foodDatum.getHealthTips());
                prep.setString(85, foodDatum.getHealthSuggest());
                prep.setBigDecimal(86, foodDatum.getGiValue());
                prep.setString(87, foodDatum.getGiLabel());
                prep.setBigDecimal(88, foodDatum.getGlValue());
                prep.setString(89, foodDatum.getGlLabel());
                prep.addBatch();
            }
            prep.executeBatch();
            prep.clearBatch();
            conn.commit();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            prep.close();
            conn.close();
        }
    }
    private static List<FoodsDetail> getFoodDetailsJson(){
        String fileName = "E:/Idea-JavaProject/student-health/sql/food_details.txt";
        List<FoodsDetail> foodsDetailsList = new ArrayList<>();
        try {
            List<String> strings = Files.readAllLines(Paths.get(fileName));
            for (String result : strings) {
                foodsDetailsList.addAll(JSON.parseObject(result, new TypeReference<List<FoodsDetail>>(){}));
            }
            return  foodsDetailsList;
        } catch (IOException e) {
            e.printStackTrace();
        }
        return  null;
    }

    private static void getFoodsDetailData(){
        try {
            List<String> foodIds = Files.readAllLines(Paths.get("E:/Idea-JavaProject/student-health/sql/foods_id.txt"));

            for (int i = 0; i<foodIds.size();i++) {
                if(i <= 3284){
                    continue;
                }
                String foodId = foodIds.get(i);
                String replace = foodId.replace("\"", "");
                //shi: app_id=wvggmpt8iprdjhtm&app_secret=Q2R5VzE4Z2o0eit0YVVCVWwzUHRQdz09
                //geren: app_id=hdcxrml7ngriekci&app_secret=0ExqdMJsmFxcUrmekQ0un9SwofY8ur7O
                String url = "https://www.mxnzp.com/api/food_heat/food/details?foodId="+replace+"&app_id=hdcxrml7ngriekci&app_secret=0ExqdMJsmFxcUrmekQ0un9SwofY8ur7O";
                String result = HttpClientUtil.doGet(url);
                JSONObject jsonObject = JSON.parseObject(result);
                JSONObject data = jsonObject.getJSONObject("data");
                FoodsDetail foodsDetail = new FoodsDetail();
                foodsDetail.setFoodId(data.getString("foodId"));
                foodsDetail.setName(data.getString("name"));
                foodsDetail.setCalory(data.getBigDecimal("calory"));
                foodsDetail.setCaloryUnit(data.getString("caloryUnit"));
                foodsDetail.setJoule(data.getBigDecimal("joule"));
                foodsDetail.setJouleUnit(data.getString("jouleUnit"));
                foodsDetail.setProtein(data.getBigDecimal("protein"));
                foodsDetail.setProteinUnit(data.getString("proteinUnit"));
                foodsDetail.setFat(data.getBigDecimal("fat"));
                foodsDetail.setFatUnit(data.getString("fatUnit"));
                foodsDetail.setSaturatedFat(data.getBigDecimal("saturatedFat"));
                foodsDetail.setSaturatedFatUnit(data.getString("saturatedFatUnit"));
                foodsDetail.setFattyAcid(data.getBigDecimal("fattyAcid"));
                foodsDetail.setFattyAcidUnit(data.getString("fattyAcidUnit"));
                foodsDetail.setMufa(data.getBigDecimal("mufa"));
                foodsDetail.setMufaUnit(data.getString("mufaUnit"));
                foodsDetail.setPufa(data.getBigDecimal("pufa"));
                foodsDetail.setPufaUnit(data.getString("pufaUnit"));
                foodsDetail.setCholesterol(data.getBigDecimal("cholesterol"));
                foodsDetail.setCholesterolUnit(data.getString("cholesterolUnit"));
                foodsDetail.setCarbohydrate(data.getBigDecimal("carbohydrate"));
                foodsDetail.setCarbohydrateUnit(data.getString("carbohydrateUnit"));
                foodsDetail.setSugar(data.getBigDecimal("sugar"));
                foodsDetail.setSugarUnit(data.getString("sugarUnit"));
                foodsDetail.setFiberDietary(data.getBigDecimal("fiberDietary"));
                foodsDetail.setFiberDietaryUnit(data.getString("fiberDietaryUnit"));
                foodsDetail.setNatrium(data.getBigDecimal("natrium"));
                foodsDetail.setNatriumUnit(data.getString("natriumUnit"));
                foodsDetail.setAlcohol(data.getBigDecimal("alcohol"));
                foodsDetail.setAlcoholUnit(data.getString("alcoholUnit"));
                foodsDetail.setVitaminA(data.getBigDecimal("vitaminA"));
                foodsDetail.setVitaminAUnit(data.getString("vitaminAUnit"));
                foodsDetail.setCarotene(data.getBigDecimal("carotene"));
                foodsDetail.setCaroteneUnit(data.getString("caroteneUnit"));
                foodsDetail.setVitaminD(data.getBigDecimal("vitaminD"));
                foodsDetail.setVitaminDUnit(data.getString("vitaminDUnit"));
                foodsDetail.setVitaminE(data.getBigDecimal("vitaminE"));
                foodsDetail.setVitaminEUnit(data.getString("vitaminEUnit"));
                foodsDetail.setVitaminK(data.getBigDecimal("vitaminK"));
                foodsDetail.setVitaminKUnit(data.getString("vitaminKUnit"));
                foodsDetail.setThiamine(data.getBigDecimal("thiamine"));
                foodsDetail.setThiamineUnit(data.getString("thiamineUnit"));
                foodsDetail.setLactoflavin(data.getBigDecimal("lactoflavin"));
                foodsDetail.setLactoflavinUnit(data.getString("lactoflavinUnit"));
                foodsDetail.setVitaminB6(data.getBigDecimal("vitaminB6"));
                foodsDetail.setVitaminB6Unit(data.getString("vitaminB6Unit"));
                foodsDetail.setVitaminB12(data.getBigDecimal("vitaminB12"));
                foodsDetail.setVitaminB12Unit(data.getString("vitaminB12Unit"));
                foodsDetail.setVitaminC(data.getBigDecimal("vitaminC"));
                foodsDetail.setVitaminCUnit(data.getString("vitaminCUnit"));
                foodsDetail.setNiacin(data.getBigDecimal("niacin"));
                foodsDetail.setNiacinUnit(data.getString("niacinUnit"));
                foodsDetail.setFolacin(data.getBigDecimal("folacin"));
                foodsDetail.setFolacinUnit(data.getString("folacinUnit"));
                foodsDetail.setPantothenic(data.getBigDecimal("pantothenic"));
                foodsDetail.setPantothenicUnit(data.getString("pantothenicUnit"));
                foodsDetail.setBiotin(data.getBigDecimal("biotin"));
                foodsDetail.setBiotinUnit(data.getString("biotinUnit"));
                foodsDetail.setCholine(data.getBigDecimal("choline"));
                foodsDetail.setCholineUnit(data.getString("cholineUnit"));
                foodsDetail.setPhosphor(data.getBigDecimal("phosphor"));
                foodsDetail.setPhosphorUnit(data.getString("phosphorUnit"));
                foodsDetail.setKalium(data.getBigDecimal("kalium"));
                foodsDetail.setKaliumUnit(data.getString("kaliumUnit"));
                foodsDetail.setMagnesium(data.getBigDecimal("magnesium"));
                foodsDetail.setMagnesiumUnit(data.getString("magnesiumUnit"));
                foodsDetail.setCalcium(data.getBigDecimal("calcium"));
                foodsDetail.setCalciumUnit(data.getString("calciumUnit"));
                foodsDetail.setIron(data.getBigDecimal("iron"));
                foodsDetail.setIronUnit(data.getString("ironUnit"));
                foodsDetail.setZinc(data.getBigDecimal("zinc"));
                foodsDetail.setZincUnit(data.getString("zincUnit"));
                foodsDetail.setIodine(data.getBigDecimal("iodine"));
                foodsDetail.setIodineUnit(data.getString("iodineUnit"));
                foodsDetail.setSelenium(data.getBigDecimal("selenium"));
                foodsDetail.setSeleniumUnit(data.getString("seleniumUnit"));
                foodsDetail.setCopper(data.getBigDecimal("copper"));
                foodsDetail.setCopperUnit(data.getString("copperUnit"));
                foodsDetail.setFluorine(data.getBigDecimal("fluorine"));
                foodsDetail.setFluorineUnit(data.getString("fluorineUnit"));
                foodsDetail.setManganese(data.getBigDecimal("manganese"));
                foodsDetail.setManganeseUnit(data.getString("manganeseUnit"));
                foodsDetail.setHealthLight(data.getInteger("healthLight"));
                foodsDetail.setHealthTips(data.getString("healthTips"));
                foodsDetail.setHealthSuggest(data.getString("healthSuggest"));
                foodsDetail.setGiValue(data.getJSONObject("glycemicInfoData").getJSONObject("gi").getBigDecimal("value"));
                foodsDetail.setGiLabel(data.getJSONObject("glycemicInfoData").getJSONObject("gi").getString("label"));
                foodsDetail.setGlValue(data.getJSONObject("glycemicInfoData").getJSONObject("gl").getBigDecimal("value"));
                foodsDetail.setGlLabel(data.getJSONObject("glycemicInfoData").getJSONObject("gl").getString("label"));


                String additionalContent = JSON.toJSONString(foodsDetail)+", \n";

                try {
                    // 设置为true表示追加内容
                    FileWriter writer = new FileWriter("E:/Idea-JavaProject/student-health/sql/food_details.txt", true);
                    writer.write(additionalContent);
                    writer.close();
                    System.out.println("内容已成功追加到文件末尾");
                } catch (IOException e) {
                    System.err.println("写入文件时出现错误: " + e.getMessage());
                }

                try {
                    Thread.sleep(1000);
                } catch (InterruptedException e) {
                    e.printStackTrace();
                }
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    private static List<Foods> getFoodDataJson(){
        String fileName = "E:/Idea-JavaProject/student-health/sql/食物JSON.txt";
        try {
            List<String> strings = Files.readAllLines(Paths.get(fileName));
            String result = strings.get(0);
            List<Foods> foodList = JSON.parseObject(result, new TypeReference<List<Foods>>(){});
            return  foodList;
        } catch (IOException e) {
            e.printStackTrace();
        }
        return  null;
    }

    private static List<Foods> getFoodData(){
        List<Foods> list = new ArrayList<>();
        for(int i=1;i<2;i++){
            for(int j=1;j<=50;j++){
                String url = "https://www.mxnzp.com/api/food_heat/food/list?id="+i+"&page="+j+"&app_id=hdcxrml7ngriekci&app_secret=0ExqdMJsmFxcUrmekQ0un9SwofY8ur7O";
                String result = HttpClientUtil.doGet(url);
                JSONObject jsonObject = JSON.parseObject(result);
                JSONObject data = jsonObject.getJSONObject("data");
                JSONArray dataList = data.getJSONArray("list");
                for (int n = 0; n < dataList.size(); n++) {
                    Foods foods = new Foods();
                    JSONObject food = dataList.getJSONObject(n);
                    foods.setFoodId(food.getString("foodId"));
                    foods.setName(food.getString("name"));
                    foods.setHealthLevel(food.getIntValue("healthLevel"));
                    foods.setCalory(food.getBigDecimal("calory"));
                    foods.setCategory(i);
                    list.add(foods);
                }
                try {
                    Thread.sleep(2000);
                } catch (InterruptedException e) {
                    e.printStackTrace();
                }
            }
        }
        return list;
    }
}
